﻿using System;
using System.IO;
using System.Linq;
using System.Windows.Forms;
using System.Xml.Serialization;

namespace SQLS
{
    public partial class frmMain : Form
    {
        public frmMain()
        {
            InitializeComponent();
        }

        private void frmMain_Load(object sender, EventArgs e)
        {
            this.Text = Application.ProductName + " v" + Application.ProductVersion + ": " + Application.CompanyName;

            //setup extensions tooltip
            string tooltipText = "1:\ttbl*.*\t\t*.tab\r\n" +
                               "2:\tidx*.*\t\t*.idx\r\n" +
                               "3:\tdata*.*\t\t*.dat\r\n" +
                               "4:\tfn*.*\t\t*.udf\r\n" +
                               "5:\tvu*.*\t\t*.viw\r\n" +
                               "6:\tsp*.*\t\t*.prc\r\n" +
                               "7:\tjob*.*\t\t*.job\r\n" +
                               "8:\t*.usr\t\t*.lgn\r\n" +
                               "9:\t*.*";

            ToolTip toolTip = new ToolTip();
            toolTip.ShowAlways = true;
            toolTip.SetToolTip(this.chkPrefixes, tooltipText);
        }

        private void btnQuit_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnBrowseIn_Click(object sender, EventArgs e)
        {
            using (FolderBrowserDialog folderDialog = new FolderBrowserDialog())
            {
                DirectoryInfo d = new DirectoryInfo(txtInputPath.Text);
                if (d.Exists)
                    folderDialog.SelectedPath = txtInputPath.Text;

                if (folderDialog.ShowDialog() == DialogResult.OK)
                    txtInputPath.Text = folderDialog.SelectedPath;
            }
        }

        private void btnBrowseOut_Click(object sender, EventArgs e)
        {
            using (FolderBrowserDialog folderDialog = new FolderBrowserDialog())
            {
                folderDialog.ShowNewFolderButton = true;

                DirectoryInfo d = new DirectoryInfo(txtOutputPath.Text);
                if (d.Exists)
                    folderDialog.SelectedPath = txtOutputPath.Text;

                if (folderDialog.ShowDialog() == DialogResult.OK)
                    txtOutputPath.Text = folderDialog.SelectedPath;
            }
        }

        private void btnSelectAll_Click(object sender, EventArgs e)
        {
            pbProgress.Minimum = 0;
            pbProgress.Maximum = lstOrdered.Items.Count;
            pbProgress.Value = 0;
            pbProgress.Visible = true;

            for (int i = 0; i < lstOrdered.Items.Count; i++)
            {
                lstOrdered.SetItemChecked(i, true);
                pbProgress.Value += 1;
            }

            pbProgress.Visible = false;
        }

        private void btnSelectNone_Click(object sender, EventArgs e)
        {
            pbProgress.Minimum = 0;
            pbProgress.Maximum = lstOrdered.Items.Count;
            pbProgress.Value = 0;
            pbProgress.Visible = true;

            for (int i = 0; i < lstOrdered.Items.Count; i++)
            {
                lstOrdered.SetItemChecked(i, false);
                pbProgress.Value += 1;
            }

            pbProgress.Visible = false;
        }

        private void btnUp_Click(object sender, EventArgs e)
        {
            if (this.lstOrdered.SelectedIndex == -1 || this.lstOrdered.SelectedIndex == 0)
                return;

            Object select, previous, temp;
            select = lstOrdered.Items[lstOrdered.SelectedIndex];
            previous = lstOrdered.Items[lstOrdered.SelectedIndex - 1];
            temp = select;
            select = previous;
            previous = temp;
            lstOrdered.Items[lstOrdered.SelectedIndex] = select;
            lstOrdered.Items[lstOrdered.SelectedIndex - 1] = previous;

            lstOrdered.SelectedIndex--;
        }

        private void btnDown_Click(object sender, EventArgs e)
        {
            if (this.lstOrdered.SelectedIndex == -1 || this.lstOrdered.SelectedIndex == lstOrdered.Items.Count - 1)
                return;

            Object select, next, temp;
            select = lstOrdered.Items[lstOrdered.SelectedIndex];
            next = lstOrdered.Items[lstOrdered.SelectedIndex + 1];
            temp = select;
            select = next;
            next = temp;
            lstOrdered.Items[lstOrdered.SelectedIndex] = select;
            lstOrdered.Items[lstOrdered.SelectedIndex + 1] = next;

            lstOrdered.SelectedIndex++;
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            pbProgress.Minimum = 0;
            pbProgress.Maximum = lstOrdered.Items.Count + 2;
            pbProgress.Value = 0;
            pbProgress.Visible = true;

            //create and fill sqls object
            sqls mySQLS = new sqls();

            mySQLS.OVSC = txtChangeNo.Text;
            mySQLS.Description = txtDescription.Text;

            mySQLS.SQLServer = txtServer.Text;
            mySQLS.Database = txtDatabase.Text;

            mySQLS.InputPath = txtInputPath.Text;
            mySQLS.OutputPath = txtOutputPath.Text;

            mySQLS.ClearOutput = chkDel.Checked;
            mySQLS.OrderByPrefix = chkPrefixes.Checked;
            mySQLS.UseInput = chkUseInput.Checked;

            //save listbox items
            for (int i = 0; i < lstOrdered.Items.Count; i++)
            {
                SQLS.SQLFile file = new SQLS.SQLFile(lstOrdered.Items[i].ToString(), lstOrdered.GetItemChecked(i));

                mySQLS.AddItem(file);

                pbProgress.Value += 1;
            }

            //get filename
            using (SaveFileDialog saveDialog = new SaveFileDialog())
            {
                //setup dialog
                saveDialog.Filter = "SQLS Files (*.sxml)|*.sxml";

                if (saveDialog.ShowDialog() == DialogResult.OK)
                {
                    //setup xml serialiser on sqls
                    XmlSerializer xs = new XmlSerializer(typeof(sqls));

                    using (TextWriter tw = new StreamWriter(saveDialog.FileName))
                    {
                        //write serialised sqls to file
                        xs.Serialize(tw, mySQLS);
                        tw.Close();
                        pbProgress.Value += 1;
                    }

                    MessageBox.Show(saveDialog.FileName + " Saved Succesfully!");
                }
            }

            pbProgress.Visible = false;
        }

        private void btnLoad_Click(object sender, EventArgs e)
        {
            pbProgress.Minimum = 0;
            pbProgress.Maximum = 100;
            pbProgress.Value = 0;
            pbProgress.Visible = true;

            using (OpenFileDialog openDialog = new OpenFileDialog())
            {
                //setup dialog
                openDialog.Filter = "SQLS Files (*.sxml)|*.sxml";
                openDialog.CheckFileExists = true;

                if (openDialog.ShowDialog() == DialogResult.OK)
                {
                    XmlSerializer xs = new XmlSerializer(typeof(sqls));

                    using (TextReader tr = new StreamReader(openDialog.FileName))
                    {
                        sqls mySQLS;
                        //read and deserialize
                        try
                        {
                            mySQLS = (sqls) xs.Deserialize(tr);

                            txtChangeNo.Text = mySQLS.OVSC;
                            txtDatabase.Text = mySQLS.Database;
                            txtDescription.Text = mySQLS.Description;
                            txtInputPath.Text = mySQLS.InputPath;
                            txtOutputPath.Text = mySQLS.OutputPath;
                            txtServer.Text = mySQLS.SQLServer;
                            chkDel.Checked = mySQLS.ClearOutput;
                            chkPrefixes.Checked = mySQLS.OrderByPrefix;
                            chkUseInput.Checked = mySQLS.UseInput;

                            lstOrdered.Items.Clear();

                            pbProgress.Maximum = mySQLS.SQLFiles.Count() + 1;

                            foreach (SQLS.SQLFile file in mySQLS.SQLFiles)
                            {
                                int i = lstOrdered.Items.Add(file.filename);
                                lstOrdered.SetItemChecked(i, file.ischecked);

                                pbProgress.Value += 1;
                            }

                        }
                        catch (InvalidOperationException ex)
                        {
                            MessageBox.Show(ex.Message, "SQLS Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                            return;
                        }

                        tr.Close();

                        pbProgress.Visible = false;

                        MessageBox.Show(openDialog.FileName + " Loaded Succesfully!");
                    }
                }
            }
        }

        private void btnPreview_Click(object sender, EventArgs e)
        {
            string filename;
            filename = txtInputPath.Text + "\\" + lstOrdered.SelectedItem.ToString();
            if (File.Exists(filename))
            {
                using (Form preview = new frmPreview(filename))
                {
                    preview.ShowDialog();
                }
            }
        }

        private void btnGetFiles_Click(object sender, EventArgs e)
        {
            DirectoryInfo dir = new DirectoryInfo(txtInputPath.Text);

            lstOrdered.Items.Clear();

            //init pb
            pbProgress.Minimum = 0;
            pbProgress.Maximum = dir.GetFiles("*.*").Count();
            pbProgress.Value = 0;
            pbProgress.Visible = true;

            if (chkPrefixes.Checked == true)
            {
                //tbl (Table Changes)
                foreach (FileInfo file in dir.GetFileSystemInfos("tbl*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.tab"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //idx (Index Changes)
                foreach (FileInfo file in dir.GetFileSystemInfos("idx*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.idx"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //data (Data Changes)
                foreach (FileInfo file in dir.GetFileSystemInfos("data*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.dat"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //fn (Functions)
                foreach (FileInfo file in dir.GetFileSystemInfos("fn*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.udf"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //vu (Views)
                foreach (FileInfo file in dir.GetFileSystemInfos("vu*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.viw"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //sp (Stored Procedures)
                foreach (FileInfo file in dir.GetFileSystemInfos("sp*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.prc"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //job (SQL Agent Jobs)
                foreach (FileInfo file in dir.GetFileSystemInfos("job*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                foreach (FileInfo file in dir.GetFileSystemInfos("*.job"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //users
                foreach (FileInfo file in dir.GetFileSystemInfos("*.usr"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
                //logons
                foreach (FileInfo file in dir.GetFileSystemInfos("*.lgn"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }

                //x (General Executions)
                foreach (FileInfo file in dir.GetFileSystemInfos("x*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
            }
            else
            {
                foreach (FileInfo file in dir.GetFileSystemInfos("*.*"))
                {
                    if (!lstOrdered.Items.Contains(file.Name))
                    {
                        lstOrdered.Items.Add(file.Name);
                        pbProgress.Value += 1;
                    }
                }
            }

            if(lstOrdered.Items.Count==0)
                MessageBox.Show("No items found!");

            pbProgress.Visible = false;
        }

        private void btnGenerate_Click(object sender, EventArgs e)
        {
            const string OSQLLINE = "osql -E -S #SERVER -d #DATABASE -n -m-1 -b -i #INPUT >> #OUTPUT";
            const string SQLCMDLINE = "sqlcmd -E -S #SERVER -d #DATABASE -i #INPUT -r1 -m-1 >> #OUTPUT";

            //validation
            if (string.IsNullOrEmpty(txtDatabase.Text) || string.IsNullOrEmpty(txtServer.Text))
            {
                MessageBox.Show("Please enter a Database name and a Server name!", "SQLS", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (!Directory.Exists(txtInputPath.Text))
            {
                MessageBox.Show("Please enter valid input paths!", "SQLS", MessageBoxButtons.OK, MessageBoxIcon.Error);
                return;
            }
            if (!chkUseInput.Checked && !Directory.Exists(txtOutputPath.Text))
            {
                if (MessageBox.Show("Output path (" + txtOutputPath.Text + ") does not exist! Would you like to create it?", "SQLS", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
                    Directory.CreateDirectory(txtOutputPath.Text);
                else
                    return;
            }

            pbProgress.Minimum = 0;
            pbProgress.Maximum = lstOrdered.Items.Count + 2;
            pbProgress.Value = 0;
            pbProgress.Visible = true;

            //delete folder
            if (chkDel.Checked && !chkUseInput.Checked)
            {
                DirectoryInfo dir = new DirectoryInfo(txtOutputPath.Text);
                foreach (FileInfo file in dir.GetFileSystemInfos())
                    file.Delete();
            }

            pbProgress.Value = 1;

            string batFile = "";
            if (!chkUseInput.Checked)
                batFile = txtOutputPath.Text;
            else
                batFile = txtInputPath.Text;
            batFile += "\\" + txtChangeNo.Text + "-" + txtDatabase.Text + "-IMP.bat";

            string outFile = txtChangeNo.Text + txtDatabase.Text + "-ERROR.txt";

            using (StreamWriter sw = new StreamWriter(batFile))
            {
                //bat header
                sw.WriteLine("@ECHO OFF");
                sw.WriteLine();

                if (chkBare.Checked == false)
                {
                    if (txtChangeNo.Text == "")
                        txtChangeNo.Text = "NONE";

                    sw.WriteLine("ECHO Implementation Script for OVSC:" + txtChangeNo.Text);
                    sw.WriteLine("ECHO " + txtDescription.Text);
                    sw.WriteLine("ECHO Server: " + txtServer.Text + " Database: " + txtDatabase.Text);
                    sw.WriteLine("ECHO If incorrect please press CTRL+C now!");
                    sw.WriteLine("pause");
                    sw.WriteLine();
                }
                pbProgress.Value = 2;

                for (int i = 0; i < lstOrdered.Items.Count; i++)
                {
                    if (lstOrdered.GetItemChecked(i))
                    {
                        string listFile = lstOrdered.Items[i].ToString();

                        //use sql 2000 osql, or sql 2005/8 sqlcmd
                        string sl = "";
                        if (radSQL0.Checked)
                            sl = OSQLLINE;
                        else
                            sl = SQLCMDLINE;

                        sl = sl.Replace("#SERVER", txtServer.Text);
                        sl = sl.Replace("#DATABASE", txtDatabase.Text);
                        sl = sl.Replace("#INPUT", "\"" + listFile + "\"");
                        sl = sl.Replace("#OUTPUT", outFile);

                        //copy file
                        if (!chkUseInput.Checked)
                            File.Copy(txtInputPath.Text + "\\" + listFile, txtOutputPath.Text + "\\" + listFile, true);

                        //print line
                        sw.WriteLine("echo " + sl.Substring(0, sl.IndexOf(">>")));
                        //seperator
                        sw.WriteLine("echo ************************************** >> " + outFile);
                        sw.WriteLine("echo " + listFile + " >> " + outFile);
                        sw.WriteLine("echo ************************************** >> " + outFile);
                        //actual osql cmd
                        sw.WriteLine(sl);
                        sw.WriteLine();

                        pbProgress.Value = i + 2;
                    }
                }

                if (chkBare.Checked == false)
                {
                    sw.WriteLine("echo All scripts run!");
                    sw.WriteLine("echo Please check the " + outFile + " for errors!");
                    sw.WriteLine("pause");
                }
                sw.Close();
            }

            pbProgress.Visible = false;
            MessageBox.Show("Generated: \r\n" + batFile);
        }

        private void chkUseInput_CheckedChanged(object sender, EventArgs e)
        {
            if (chkUseInput.Checked == true)
            {
                btnBrowseOut.Enabled = false;
                txtOutputPath.Enabled = false;
                chkDel.Checked = false;
                chkDel.Enabled = false;
            }
            else
            {
                btnBrowseOut.Enabled = true;
                txtOutputPath.Enabled = true;
                chkDel.Enabled = true;
            }
        }

        private void chkBare_CheckedChanged(object sender, EventArgs e)
        {
            if (chkBare.Checked == true)
            {
                txtChangeNo.Enabled = false;
                txtDescription.Enabled = false;
            }
            else
            {
                txtChangeNo.Enabled = true;
                txtDescription.Enabled = true;
            }
        }

        private void radSQL0_CheckedChanged(object sender, EventArgs e)
        {
            if (radSQL0.Checked)
                radSQL58.Checked = false;
        }

        private void radSQL58_CheckedChanged(object sender, EventArgs e)
        {
            if (radSQL58.Checked)
                radSQL0.Checked = false;
        }
    }
}
